package com.bishe.tax.controller.sys;

import com.bishe.tax.entity.Ledger;
import com.bishe.tax.entity.LedgerArticle;
import com.bishe.tax.service.FileUploadService;
import com.bishe.tax.service.LedgerArticleService;
import com.bishe.tax.service.LedgerService;
import com.bishe.tax.utils.AjaxResult;
import com.bishe.tax.utils.ExcelManager;
import com.bishe.tax.utils.TableDataInfo;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * 账薄 信息操作处理
 *
 * @author ruoyi
 * @date 2019-06-23
 */
@Controller
@RequestMapping("/system/ledger")
public class LedgerController {
    private String prefix = "ledger";

    @Autowired
    private LedgerService ledgerService;

    @Autowired
    private LedgerArticleService articleService;

    @Autowired
    private FileUploadService fileUploadService;

    @GetMapping("list")
    public String ledger(@RequestParam(value = "taxpayerName", required = false) String taxpayerName,
                         @RequestParam(value = "ledgerTaxpayer", required = false) String ledgerTaxpayer, ModelMap mmap) throws Exception {
        mmap.put("taxpayerName", new String(taxpayerName.getBytes("ISO8859-1"), "UTF-8"));
        mmap.put("ledgerTaxpayer", ledgerTaxpayer);
        return prefix + "/list";
    }

    @GetMapping("trList")
    public String trList() {
        return prefix + "/trList";
    }

    @GetMapping("statList")
    public String statList(ModelMap mmap) {
        return prefix + "/statList";
    }

    @GetMapping("listday")
    public String listday(@RequestParam(value = "taxpayerId", required = false) String taxpayerId,
                          @RequestParam(value = "taxpayerName", required = false) String taxpayerName, ModelMap mmap) throws Exception {
        mmap.put("taxpayerId", taxpayerId);
        mmap.put("taxpayerName", new String(taxpayerName.getBytes("ISO8859-1"), "UTF-8"));
        return prefix + "/listday";
    }

    @GetMapping("listmodel")
    public String listmodel(@RequestParam(value = "taxpayerId", required = false) String taxpayerId,
                            @RequestParam(value = "taxpayerName", required = false) String taxpayerName, ModelMap mmap) throws Exception {
        mmap.put("taxpayerId", taxpayerId);
        mmap.put("taxpayerName", new String(taxpayerName.getBytes("ISO8859-1"), "UTF-8"));
        return prefix + "/listmodel";
    }

    /**
     * 查询账薄列表
     */
    @PostMapping("/list")
    @ResponseBody
    public TableDataInfo list(Ledger ledger, @RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize,
                              @RequestParam(value = "pageNum", defaultValue = "1") Integer pageNum) {
        PageHelper.startPage(pageNum, pageSize);
        List<Ledger> list = ledgerService.selectLedgerList(ledger);
        return TableDataInfo.returnDataTable(new PageInfo<>(list).getTotal(), list);
    }

    /**
     * 查询账薄列表
     */
    @PostMapping("/statisticList")
    @ResponseBody
    public TableDataInfo statisticList(@RequestParam(value = "taxpayerName", required = false) String taxpayerName,
                                       @RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize,
                                       @RequestParam(value = "pageNum", defaultValue = "1") Integer pageNum) {
        PageHelper.startPage(pageNum, pageSize);
        List<Map<String, Object>> list = ledgerService.selectLedgerStatistics(taxpayerName);
        return TableDataInfo.returnDataTable(new PageInfo<>(list).getTotal(), list);
    }

    /**
     * 查询账薄列表
     */
    @PostMapping("/listday")
    @ResponseBody
    public TableDataInfo dayList(@RequestParam(value = "taxpayerId", required = false) String taxpayerId,
                                 @RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize,
                                 @RequestParam(value = "pageNum", defaultValue = "1") Integer pageNum) {
        PageHelper.startPage(pageNum, pageSize);
        List<Map<String, Object>> list = ledgerService.selectLedgerByDay(Integer.valueOf(taxpayerId));
        return TableDataInfo.returnDataTable(new PageInfo<>(list).getTotal(), list);
    }

    /**
     * 查询账薄列表
     */
    @PostMapping("/listmodel")
    @ResponseBody
    public TableDataInfo modelList(@RequestParam(value = "taxpayerId", required = false) String taxpayerId,
                                   @RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize,
                                   @RequestParam(value = "pageNum", defaultValue = "1") Integer pageNum) {
        PageHelper.startPage(pageNum, pageSize);
        List<Map<String, Object>> list = ledgerService.selectLedgerByModel(Integer.valueOf(taxpayerId));
        return TableDataInfo.returnDataTable(new PageInfo<>(list).getTotal(), list);
    }

    /**
     * 新增账薄
     */
    @GetMapping("/add")
    public String add(@RequestParam("taxpayer") String taxpayer, ModelMap mmap) {
        mmap.put("taxpayerId", taxpayer);
        return prefix + "/add";
    }

    /**
     * 新增保存账薄
     */
    @PostMapping("/add")
    @ResponseBody
    public AjaxResult addSave(Ledger ledger) {
        return AjaxResult.success(ledgerService.insertLedger(ledger));
    }

    /**
     * 修改账薄
     */
    @GetMapping("/edit/{ledgerId}")
    public String edit(@PathVariable("ledgerId") Integer ledgerId, ModelMap mmap) {
        Ledger ledger = ledgerService.selectLedgerById(ledgerId);
        mmap.put("ledger", ledger);
        return prefix + "/edit";
    }

    /**
     * 修改保存账薄
     */
    @PostMapping("/edit")
    @ResponseBody
    public AjaxResult editSave(Ledger ledger) {
        return AjaxResult.success(ledgerService.updateLedger(ledger));
    }

    /**
     * 删除账薄
     */
    @PostMapping("/remove")
    @ResponseBody
    public AjaxResult remove(String ids) {
        return AjaxResult.success(ledgerService.deleteLedgerByIds(ids));
    }

    /**
     * 上传账薄
     *
     * @throws Exception
     */
    @RequestMapping("upload")
    @ResponseBody
    public AjaxResult upload(MultipartFile file, @RequestParam("taxpayerId") Integer taxpayerId) {
        try {
            String path = fileUploadService.upLoadFile(file);
            // 文件名
            String filename = file.getOriginalFilename();
            // 后缀名
            String suffix = filename.substring(filename.lastIndexOf("."));
            // 输入流
            InputStream is = file.getInputStream();
            Workbook workbook = null;
            if (suffix.equals(".xlsx")) {
                workbook = new XSSFWorkbook(is);// 创建 一个excel文档对象
            } else if (suffix.equals(".xls")) {
                workbook = new HSSFWorkbook(is);// 创建 一个excel文档对象
            }
            // 第三步 获得sheet对象
            Sheet sheet = workbook.getSheetAt(0); // 第一个
            // 操作数据
            Iterator<Row> rowIterator = sheet.rowIterator();
            rowIterator.next(); // 跳过第一行
            String str = "";
            Ledger ledger = null;
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();// 每行 ---- 对应分区信息
                SimpleDateFormat sdf = null;
                sdf = new SimpleDateFormat("yyyy-MM-dd"); // 设置转成的时间格式
                Date date = row.getCell(0).getDateCellValue();// 取得的是date类型
                String ledgerTime = sdf.format(date); // 时间

                String ledgerRemake = row.getCell(1) == null ? "" : row.getCell(1).getStringCellValue();// 摘要
                String ledgerDebit = String.valueOf(new DecimalFormat("#.00")
                        .format(row.getCell(2) == null ? 0 : row.getCell(2).getNumericCellValue()));// 贷记
                String ledgerRedit = String.valueOf(new DecimalFormat("#.00")
                        .format(row.getCell(3) == null ? 0 : row.getCell(3).getNumericCellValue()));// 借记
                String ledgerBalance = String.valueOf(new DecimalFormat("#.00")
                        .format(row.getCell(4) == null ? 0 : row.getCell(4).getNumericCellValue()));// 总额
                String ledgerType = row.getCell(5) == null ? "" : row.getCell(5).getStringCellValue();// 类型
                String ledgerModel = row.getCell(6) == null ? "" : row.getCell(6).getStringCellValue();// 科目
                ledger = new Ledger();

                ledger.setLedgerTime(new SimpleDateFormat("yyyy-MM-dd").parse(ledgerTime));
                ledger.setLedgerDebit(new BigDecimal(ledgerDebit));
                ledger.setLedgerBalance(new BigDecimal(ledgerBalance));
                ledger.setLedgerRedit(new BigDecimal(ledgerRedit));
                ledger.setLedgerType(ledgerType);
                ledger.setRemark(ledgerRemake);
                ledger.setLedgerPath(path);
                ledger.setLedgerTaxpayer(taxpayerId);
                ledger.setLedgerModel(ledgerModel);
                ledgerService.insertLedger(ledger);
            }

            excelTotal(taxpayerId);
            excelModel(taxpayerId);
            excelDay(taxpayerId);
            return AjaxResult.success();
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.error();
        }
    }

    private void excelTotal(Integer taxpayerId) {
        String filePath = "D:/taxPoi/result/";
        String fileName = new SimpleDateFormat("yyyy年MM月dd日HH时mm分ss秒").format(new Date()) + "-总账-" + taxpayerId + ".xls";
        String sheetName = "总账";
        // Excel文件易车sheet页的第一行
        String title[] = {"时间", "借记", "贷记", "总额", "类型", "科目"};
        ExcelManager em = new ExcelManager();
        // 判断该名称的文件是否存在
        boolean fileFlag = em.fileExist(filePath, fileName);
        if (!fileFlag) {
            em.createExcel(filePath + fileName, sheetName, title);
        }
        filePath += fileName;
        // 判断该名称的Sheet是否存在
        boolean sheetFlag = em.sheetExist(filePath, sheetName);
        // 如果该名称的Sheet不存在，则新建一个新的Sheet
        if (!sheetFlag) {
            try {
                em.createSheet(filePath, sheetName, title);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        Ledger lw = new Ledger();
        lw.setLedgerTaxpayer(taxpayerId);
        List<Ledger> list = ledgerService.selectLedgerList(lw);
        for (Ledger ld : list) {
            String array[] = {new SimpleDateFormat("yyyy-MM-dd").format(ld.getLedgerTime()),
                    ld.getLedgerDebit().toString(), ld.getLedgerRedit().toString(), ld.getLedgerBalance().toString(),
                    ld.getLedgerType(), ld.getLedgerModel()};
            // 写入到excel
            em.writeToExcel(filePath, sheetName, array);
        }
        LedgerArticle la = new LedgerArticle();
        la.setLaLedgerPath(filePath);
        la.setLaLedgerTaxpayer(taxpayerId);
        la.setLaLedgerType(1);
        articleService.insertArticle(la);
    }

    private void excelDay(Integer taxpayerId) {
        String filePath = "D:/taxPoi/result/";
        String fileName = new SimpleDateFormat("yyyy年MM月dd日HH时mm分ss秒").format(new Date()) + "-日记账-" + taxpayerId + ".xls";
        String sheetName = "账簿数据";
        // Excel文件易车sheet页的第一行
        String title[] = {"时间", "借记", "贷记"};
        ExcelManager em = new ExcelManager();
        // 判断该名称的文件是否存在
        boolean fileFlag = em.fileExist(filePath, fileName);
        if (!fileFlag) {
            em.createExcel(filePath + fileName, sheetName, title);
        }
        filePath += fileName;
        // 判断该名称的Sheet是否存在
        boolean sheetFlag = em.sheetExist(filePath, sheetName);
        // 如果该名称的Sheet不存在，则新建一个新的Sheet
        if (!sheetFlag) {
            try {
                em.createSheet(filePath, sheetName, title);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        List<Map<String, Object>> list = ledgerService.selectLedgerByDay(taxpayerId);
        for (Map<String, Object> map : list) {
            String array[] = {(String) map.get("ledgerTime"), ((BigDecimal) map.get("ledgerDebit")).toString(),
                    ((BigDecimal) map.get("ledgerRedit")).toString()};
            // 写入到excel
            em.writeToExcel(filePath, sheetName, array);
        }
        LedgerArticle la = new LedgerArticle();
        la.setLaLedgerPath(filePath);
        la.setLaLedgerTaxpayer(taxpayerId);
        la.setLaLedgerType(2);
        articleService.insertArticle(la);
    }

    private void excelModel(Integer taxpayerId) {
        String filePath = "D:/taxPoi/result/";
        String fileName = new SimpleDateFormat("yyyy年MM月dd日HH时mm分ss秒").format(new Date()) + "-明细账-" + taxpayerId + ".xls";
        String sheetName = "总账";
        // Excel文件易车sheet页的第一行
        String title[] = {"科目", "借记", "贷记"};
        ExcelManager em = new ExcelManager();
        // 判断该名称的文件是否存在
        boolean fileFlag = em.fileExist(filePath, fileName);
        if (!fileFlag) {
            em.createExcel(filePath + fileName, sheetName, title);
        }
        filePath += fileName;
        // 判断该名称的Sheet是否存在
        boolean sheetFlag = em.sheetExist(filePath, sheetName);
        // 如果该名称的Sheet不存在，则新建一个新的Sheet
        if (!sheetFlag) {
            try {
                em.createSheet(filePath, sheetName, title);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        List<Map<String, Object>> list = ledgerService.selectLedgerByModel(taxpayerId);
        for (Map<String, Object> map : list) {
            String array[] = {(String) map.get("ledgerModel"), ((BigDecimal) map.get("ledgerDebit")).toString(),
                    ((BigDecimal) map.get("ledgerRedit")).toString()};
            // 写入到excel
            em.writeToExcel(filePath, sheetName, array);
        }
        LedgerArticle la = new LedgerArticle();
        la.setLaLedgerPath(filePath);
        la.setLaLedgerTaxpayer(taxpayerId);
        la.setLaLedgerType(3);
        articleService.insertArticle(la);
    }
}
